﻿using NCP.Core.DataUtil;
using NCP.Core.IoC;
using NCP.Model;
using NCP.Model.eChart;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Web.UI;

namespace NCP.Service
{
    public class ReportService : IService
    {
        private SqlSugarClient _client;
        public ReportService()
        {
            _client = DbHelper.GetSqlSugarClient();
        }

        public List<VDrugCompare> GetList(string setpc,Expressionable<VDrugCompare> whereExpression, PageModel page, string orderFields = "DrugCode")
        {
            int count = 0;
           
            if (setpc == "0" || setpc==null)//全部
            {
                var result = _client.Queryable<VDrugCompare>().Where(whereExpression.ToExpression())
                    .OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                result.ForEach(t =>
                {
                    t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                    t.FInNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.UDrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                });
                page.TotalCount = count;
                return result;
            }
            else if (setpc == "1")//正在执行
            {
               var result = _client.Queryable<VDrugCompare>()
                .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && SqlFunc.Between(DateTime.Now, d.BeginDate, d.EndDate))
                .Where(whereExpression.ToExpression())
                .OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                result.ForEach(t =>
                {
                    t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                    t.FInNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.UDrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                });
                page.TotalCount = count;
                return result;
            }
            else//已到期
            {
               var result = _client.Queryable<VDrugCompare>()
                .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && ( d.BeginDate>DateTime.Now ||  d.EndDate<=DateTime.Now))
                .Where(whereExpression.ToExpression())
                .OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                result.ForEach(t =>
                {
                    t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                    t.FInNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.UDrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                });
                page.TotalCount = count;
                return result;
            }

         
        }

        public List<VVarietyDrugCompare> GetVarietyList(Expressionable<VVarietyDrugCompare> whereExpression, PageModel page, string orderFields = "DrugCode")
        {
            int count = 0;
            var result = _client.Queryable<VVarietyDrugCompare>().Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
            result.ForEach(t =>
            {
                t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                t.UseNum = _client.Queryable<PhaMedicalList>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.FeeDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                decimal totalNum = _client.Queryable<GroupVarietyDrugCompare>().InnerJoin<PhaMedicalList>((g, d) => g.VarietyDrugCode == d.DrugCode)
                    .Where((g, d) => g.ChoseDrugCode == t.DrugCode && g.GpCode == t.GpCode && SqlFunc.Between(d.FeeDate, t.BeginDate, t.EndDate))
                    .Sum((g, d) => d.Qty * g.Ratio);//非中选药品使用量
                t.UseRatio = Math.Round(totalNum == 0 ? 100 : t.UseNum * 100 / (totalNum + t.UseNum), 2).ToString() + "%";
            });
            page.TotalCount = count;
            return result;
        }
        //获取采购数据按时间筛选
        public List<VPurDrugCompare> GetVPurDrugList(string BeginDate,string EndDate, Expressionable<VPurDrugCompare> whereExpression, PageModel page, string orderFields = "DrugCode")
        {
            int count = 0;
            var result = _client.Queryable<VPurDrugCompare>().Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
            result.ForEach(t =>
            {
                t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, BeginDate, EndDate)).Sum(p => p.Qty);
               
            });
            page.TotalCount = count;
            return result;
        }
        //获取人员科室
        public List<string> getUserDeptCode(string operCode)
        {
           var data = _client.Queryable<UserDeptPermission>().Where(f=>f.Code==operCode)
                .Select(f => f.DeptID)
                .ToList();
            return data;
        }
        //查看任务科室完成情况
        public List<VDeptDrugCompare> GetDeptList(string setpc, Expressionable<VDeptDrugCompare> whereExpression, PageModel page, string orderFields = "GpCode")
        {
            int count = 0;
            if (setpc == "0" || setpc == null)//全部
            {
                var result = _client.Queryable<VDeptDrugCompare>()
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
            else if (setpc == "1")
            {
                var result = _client.Queryable<VDeptDrugCompare>()
                    .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && SqlFunc.Between(DateTime.Now, d.BeginDate, d.EndDate))
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;

            }
            else
            {
                var result = _client.Queryable<VDeptDrugCompare>()
                    .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && (d.BeginDate > DateTime.Now || d.EndDate <= DateTime.Now))
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
        }
        //查看所有科室完成情况
        public List<VALLDeptDrugCompare> GetALLDeptList(string setpc, Expressionable<VALLDeptDrugCompare> whereExpression, PageModel page, string orderFields = "GpCode")
        {
            int count = 0;
            if (setpc == "0" || setpc == null)//全部
            {
                var result = _client.Queryable<VALLDeptDrugCompare>().Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
            else if (setpc == "1")
            {
                var result = _client.Queryable<VALLDeptDrugCompare>()
                    .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && SqlFunc.Between(DateTime.Now, d.BeginDate, d.EndDate))
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;

            }
            else
            {
                var result = _client.Queryable<VALLDeptDrugCompare>()
                    .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && (d.BeginDate > DateTime.Now || d.EndDate <= DateTime.Now))
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
        }
        public List<VDocDrugCompare> GetDocList(Expressionable<VDocDrugCompare> whereExpression, PageModel page, string orderFields = "DrugCode")
        {
            int count = 0;
            var result = _client.Queryable<VDocDrugCompare>().Where(whereExpression.ToExpression()).OrderBy(orderFields).ToPageList(page.PageIndex, page.PageSize, ref count);
            result.ForEach(t =>
            {
                t.UseNum = _client.Queryable<PhaMedicalList>().Where(p => p.DrugCode == t.DrugCode && p.DeptCode == t.DeptCode && p.DoctorCode == t.DoctorCode && SqlFunc.Between(p.FeeDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                decimal totalNum = _client.Queryable<GroupChoseDrugCompare>().InnerJoin<PhaMedicalList>((g, d) => g.UnChosedrugCode == d.DrugCode)
                    .Where((g, d) => g.ChosedrugCode == t.DrugCode && g.GpCode == t.GpCode && d.DeptCode == t.DeptCode && SqlFunc.Between(d.FeeDate, t.BeginDate, t.EndDate)
                    && d.DoctorCode == t.DoctorCode
                    )
                    .Sum((g, d) => d.Qty * g.Ratio);//非中选药品使用量
                t.UseRatio = Math.Round(totalNum == 0 ? 0 : t.UseNum * 100 / (totalNum + t.UseNum), 2).ToString() + "%";
            });
            page.TotalCount = count;
            return result;
        }


        public List<GroupChartData> GetEcharts1()
        {
            //int groupYear = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1").Max(x => x.Year);
            //var group = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1" && x.Year == groupYear).First();
            List<GroupChartData> chartDatas = new List<GroupChartData>();
            var items = _client.SqlQueryable<dynamic>(@"SELECT p.Name, t.DrugCode, t.TaskQTY, t.TaskRatio 
            , (SELECT b.DrugName FROM PhaBaseInfo b WHERE t.DrugCode=b.DrugCode) AS DrugName
	        , isnull(dbo.F_CalcuQty(t.DrugCode, p.BeginDate, p.EndDate), 0) AS UseNum
	        , isnull(dbo.F_CalcuNoSelectQty(t.DrugCode, t.GpCode, p.BeginDate, p.EndDate), 0) AS UnChoseUseNum
            FROM GroupDrugInfo t
	        JOIN GroupPurchase p ON t.GpCode = p.Code
            WHERE p.BeginDate<GETDATE() and p.EndDate>GETDATE()").ToList();
            foreach (dynamic item in items)
            {
                GroupChartData chartData = chartDatas.Find(t=>t.GroupName==item.Name);
                bool exist = true;
                if (chartData == null)
                {
                    exist = false;
                    chartData = new GroupChartData();
                    chartData.GroupName = item.Name;
                    chartData.cNum = 0;
                    chartData.iNum = 0;
                }
                decimal useRatio = item.UseNum + item.UnChoseUseNum > 0 ? item.UseNum / (item.UseNum + item.UnChoseUseNum) : 0;
                if (item.TaskQTY > item.UseNum || useRatio < item.TaskRatio)
                {
                    chartData.iNum++;
                }
                else
                {
                    chartData.cNum++;
                }
                if(!exist)
                    chartDatas.Add(chartData);

            }


            return chartDatas;
        }


        public object GetEcharts2()
        {
            //int groupYear = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1").Max(x => x.Year);
            //var group = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1" && x.Year == groupYear).First();
            int groupYear = _client.Queryable<GroupPurchase>().Max(x => x.Year);
            var group = _client.Queryable<GroupPurchase>().Where(x => x.Year == groupYear).First();


            var items = _client.SqlQueryable<dynamic>(@"SELECT t.DrugCode, t.TaskQTY, t.TaskRatio 
    , (SELECT b.DrugName FROM PhaBaseInfo b WHERE t.DrugCode=b.DrugCode) AS DrugName
	, isnull(dbo.F_CalcuQty(t.DrugCode, p.BeginDate, p.EndDate), 0) AS UseNum
	, isnull(dbo.F_CalcuNoSelectQty(t.DrugCode, t.GpCode, p.BeginDate, p.EndDate), 0) AS UnChoseUseNum
FROM GroupDrugInfo t
	JOIN GroupPurchase p ON t.GpCode = p.Code
WHERE p.Code = @gpcode").AddParameters(new { gpcode = group.Code }).ToList();
            List<string> drugNames = new List<string>();
            List<decimal> drugUseRatio = new List<decimal>();
            List<decimal> drugTaskRatio = new List<decimal>();
            foreach (dynamic item in items)
            {
                decimal useRatio = item.UseNum + item.UnChoseUseNum > 0 ? item.UseNum / (item.UseNum + item.UnChoseUseNum) : 0;
                if (item.TaskQTY > item.UseNum || useRatio < item.TaskRatio)
                {
                    drugNames.Add(item.DrugName + $"(规定使用占比:{Convert.ToDouble(item.TaskRatio * 100)}%)");
                    drugUseRatio.Add(decimal.Round(useRatio * 100, 2));
                    drugTaskRatio.Add(decimal.Round(item.UseNum / item.TaskQTY * 100, 2));
                }
            }
            dynamic result = new
            {
                groupCode = group.Code,
                groupName = group.Year + "年度" + group.Name,
                drugNames,
                drugTaskRatio,
                drugUseRatio
            };


            return result;
        }


        public object GetEcharts3()
        {
            //int groupYear = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1").Max(x => x.Year);
            //var group = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1" && x.Year == groupYear).First();
            int groupYear = _client.Queryable<GroupPurchase>().Max(x => x.Year);
            var group = _client.Queryable<GroupPurchase>().Where(x => x.Year == groupYear).First();


            var items = _client.SqlQueryable<dynamic>(@"SELECT t.DrugCode, gm.Qyt AS TaskQTY, gm.Rate AS TaskRatio, gm.DeptCode, gm.DeptName
    , (SELECT b.DrugName FROM PhaBaseInfo b WHERE t.DrugCode=b.DrugCode) AS DrugName
	, isnull(dbo.F_CalcuDeptQty(t.DrugCode,gm.DeptCode, p.BeginDate, p.EndDate), 0) AS UseNum
	, isnull(dbo.F_CalcuDeptNoSelectQty(t.DrugCode, t.GpCode, gm.DeptCode, p.BeginDate, p.EndDate), 0) AS UnChoseUseNum
FROM GroupDrugInfo t
	JOIN GroupPurchase p ON t.GpCode = p.Code
	JOIN GroupMission gm ON t.GpCode = gm.GpCode AND t.DrugCode = gm.DrugCode 
WHERE p.Code = @gpcode").AddParameters(new { gpcode = group.Code }).ToList();
            List<string> deptCodes = new List<string>();
            List<string> depts = new List<string>();
            List<decimal> chars1 = new List<decimal>();
            List<decimal> chars2 = new List<decimal>();
            foreach (dynamic item in items)
            {
                if (!deptCodes.Contains(item.DeptCode))
                {
                    deptCodes.Add(item.DeptCode);
                }
            }
            foreach (var deptCode in deptCodes)
            {
                var deptItems = items.FindAll(x => x.DeptCode == deptCode);
                if (deptItems.Count > 0)
                {
                    decimal char1 = 0;//未完成数量
                    decimal char2 = 0;//已完成数量 
                    foreach (var item in deptItems)
                    {
                        decimal useRatio = item.UseNum + item.UnChoseUseNum > 0 ? item.UseNum / (item.UseNum + item.UnChoseUseNum) : 0;
                        if (item.TaskQTY > item.UseNum || useRatio < item.TaskRatio)
                        {
                            char1++;
                        }
                        else
                        {
                            char2++;
                        }
                    }
                    depts.Add(deptItems[0].DeptName);
                    chars1.Add(char1);
                    chars2.Add(char2);
                }

            }
            dynamic result = new
            {
                groupCode = group.Code,
                groupName = group.Year + "年度" + group.Name,
                depts,
                chars1,
                chars2
            };


            return result;
        }


        public dynamic GetEcharts4()
        {
            //int groupYear = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1").Max(x => x.Year);
            //var group = _client.Queryable<GroupPurchase>().Where(x => x.Enabled == "1" && x.Year == groupYear).First();
            List<GroupChartData> chartDatas = new List<GroupChartData>();
            var items = _client.SqlQueryable<dynamic>(@"SELECT p.Name, t.DrugCode, t.TaskQTY, t.TaskRatio 
            , (SELECT b.DrugName FROM PhaBaseInfo b WHERE t.DrugCode=b.DrugCode) AS DrugName
	        , isnull(dbo.F_CalcuQty(t.DrugCode, p.BeginDate, p.EndDate), 0) AS UseNum
	        , isnull(dbo.F_CalcuNoSelectQty(t.DrugCode, t.GpCode, p.BeginDate, p.EndDate), 0) AS UnChoseUseNum
            FROM GroupDrugInfo t
	        JOIN GroupPurchase p ON t.GpCode = p.Code
            WHERE p.BeginDate<GETDATE() and p.EndDate>GETDATE()").ToList();
            int wc = 0;
            int wwc = 0;
            foreach (dynamic item in items)
            {
                decimal useRatio = item.UseNum + item.UnChoseUseNum > 0 ? item.UseNum / (item.UseNum + item.UnChoseUseNum) : 0;
                if (item.TaskQTY > item.UseNum || useRatio < item.TaskRatio)
                {
                    wwc++;
                }
                else
                {
                    wc++;
                }
               

            }


            return new { wc = wc, wwc = wwc };
        }

        //获取未完成药品信息（药剂科（全院）,其他：本科室）
        public List<VDrugCompare> GetWholeWarnList(Expressionable<VDrugCompare> whereExpression,int days,string orderFields = "GpCode")
        {
            var result = _client.Queryable<VDrugCompare>()
                 .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code && SqlFunc.Between(d.EndDate,  DateTime.Now, DateTime.Now.AddDays(days)))
                 .Where(whereExpression.ToExpression())
                 .OrderBy(orderFields).ToList();
            result.ForEach(t =>
            {
                t.InNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.DrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
                t.FInNum = _client.Queryable<PhaDrugPurchase>().Where(p => p.DrugCode == t.UDrugCode && SqlFunc.Between(p.OperDate, t.BeginDate, t.EndDate)).Sum(p => p.Qty);
            });
            return result;


        }

        public List<VDeptDrugCompare> GetDeptWarnList(int days, Expressionable<VDeptDrugCompare> whereExpression, string orderFields = "GpCode")
        {
            var result = _client.Queryable<VDeptDrugCompare>()
                    .InnerJoin<GroupPurchase>((t, d) => t.GpCode == d.Code &&  SqlFunc.Between(d.EndDate, DateTime.Now, DateTime.Now.AddDays(days)))
                    .Where(whereExpression.ToExpression()).OrderBy(orderFields).ToList();
            return result;
        }

        //获取未完成科室信息（针对药剂科）

    }
}
